﻿using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;

namespace Hukee.ExcelHelper.EPPlusExtensions
{
    public class ExcelHelper
    {
        /// <summary>
        /// 从Excel文件中读取数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="file"></param>
        /// <returns></returns>
        public static List<T> GetDataListByExcel<T>(Stream file) where T : IExcelImportDto, new()
        {
            List<T> list = new List<T>();

            // EPpluse
            using (ExcelPackage package = new ExcelPackage(file))
            {
                // 只读首页的Excel
                var sheet = package.Workbook.Worksheets[0];
                var objType = typeof(T);
                var props = objType.GetProperties();
                var excelPropertyDic = new Dictionary<int, PropertyInfo>();
                if ((sheet.Dimension.End.Row - sheet.Dimension.Start.Row) <= 0) return list;
                var rfHelper = new ReflectionHelper();
                // 获取首行数据头
                for (int j = sheet.Dimension.Start.Column, k = sheet.Dimension.End.Column; j <= k; j++)
                {
                    var cellValue = sheet.GetValue(1, j)?.ToString();
                    var propInfo = rfHelper.GetPropertyByExcelImportColumnName(props, cellValue);
                    if (propInfo != null) excelPropertyDic.Add(j, propInfo);
                }
                if (excelPropertyDic.Keys.Count <= 0) return list;
                // 获取excel数据
                for (int m = sheet.Dimension.Start.Row + 1, n = sheet.Dimension.End.Row; m <= n; m++)
                {
                    T data = new T();
                    for (int j = sheet.Dimension.Start.Column, k = sheet.Dimension.End.Column; j <= k; j++)
                    {
                        if (!excelPropertyDic.ContainsKey(j)) continue;
                        try
                        {
                            rfHelper.SetValue(sheet.GetValue(m, j)?.ToString(), excelPropertyDic[j], data);
                        }
                        catch (Exception e) // 属性里throw的异常会被覆盖一层，从而取不到目标异常
                        {
                            if (e.InnerException != null) throw e.InnerException;
                            else throw e;
                        }
                    }
                    list.Add(data);
                }

                file.Close();
            }
            return list;
        }
    }

    public static class EPPlusExtensions
    {
        /// <summary>
        /// 导出Excel字节数据,字节可用作文件流输出 *列表所属类 必须有[ExcelFile]与[ExcelColumn]特性修饰过
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entities">必须有[ExcelFile]与[ExcelColumn]特性修饰过的类列表</param>
        /// <param name="needMerge">是否合并单元格</param>
        /// <param name="needToZip">是否压缩</param>
        /// <returns></returns>
        public static byte[] ToExcelBytesByEPPlus<T>(this ICollection<T> entities, bool needMerge = false) where T : class
        {
            if (entities == null || entities.Count == 0) throw new Exception("输入的参数列表不能为空");
            var excelResult = entities.ToExcelSheetData();
            excelResult.NeedMerge = needMerge;
            var data = new ExcelData(new List<ExcelSheetData> { excelResult }, excelResult.ExcelFileName);
            return ToExcelFileBytes(data);
        }

        /// <summary>
        /// 导出Excel字节数据
        /// </summary>
        /// <returns></returns>
        public static byte[] ToExcelBytesByEPPlus(this ExcelData excelData)
        {
            return ToExcelFileBytes(excelData);
        }

        private static byte[] ToExcelFileBytes(ExcelData excelData)
        {
            using (ExcelPackage package = new ExcelPackage())
            {
                var sheetItem = excelData.GetSheetList();
                var sheetNameDic = new Dictionary<string, int>();
                foreach (var excelResult in sheetItem)
                {
                    var sheetName = string.Empty;
                    if (sheetNameDic.ContainsKey(excelResult.ExcelSheetName))
                    {
                        sheetName = excelResult.ExcelSheetName + "_" + sheetNameDic[excelResult.ExcelSheetName]++;
                    }
                    else { sheetName = excelResult.ExcelSheetName; sheetNameDic.Add(sheetName, 1); }

                    ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(sheetName);
                    ASCIIEncoding ascii = new ASCIIEncoding();
                    var styleDic = excelResult.ExcelStyleDic;
                    //数据列输入 以先列再行处理
                    for (int i = 0; i < excelResult.ColumnLength(); i++)
                    {
                        var styleArray = styleDic.ContainsKey(i) ? styleDic[i] : null;
                        ExcelStyle titleStyle = null;
                        ExcelStyle normalStyle = null;
                        if (styleArray != null && styleArray.Length > 0)
                        {
                            titleStyle = styleArray.Where(t => (t.ToFormat & FormatType.Title) == FormatType.Title).FirstOrDefault();
                            normalStyle = styleArray.Where(t => (t.ToFormat & FormatType.Normal) == FormatType.Normal).FirstOrDefault();
                        }
                        double maxWidth = 0;
                        for (int j = 0; j < excelResult.RowLength(); j++)
                        {
                            worksheet.Row(j + 1).CustomHeight = true;//自适应高度
                            var cell = worksheet.Cells[j + 1, i + 1];

                            if (j == 0)//标题字体设置
                            {
                                if (titleStyle != null) SetCellStyle(cell, normalStyle);
                                else
                                {
                                    cell.Style.Font.Bold = true;//标题字体为粗体
                                    cell.Style.Font.Size = 12;//字体大小
                                    cell.Style.Font.Name = "宋体";//字体
                                }
                            }
                            else if (normalStyle != null) SetCellStyle(cell, normalStyle);
                            else cell.Style.Font.Name = "宋体";

                            if (excelResult[j, i].GetType() == typeof(int))
                            {
                                cell.Style.Numberformat.Format = "0";
                            }
                            else if (excelResult[j, i].GetType() == typeof(double) || excelResult[j, i].GetType() == typeof(float) || excelResult[j, i].GetType() == typeof(decimal))
                            {
                                cell.Style.Numberformat.Format = "0.00000";
                            }
                            cell.Value = excelResult[j, i];

                            var length = StrLengthByASCII(excelResult[j, i].ToString(), ascii);
                            if (maxWidth < length) maxWidth = length;
                        }
                        worksheet.Column(i + 1).Width = Math.Min(maxWidth + 1.2, 30 * 260); // 自适应宽度设置
                    }

                    //合并列生成
                    if (excelResult.NeedMerge) excelResult.MergeInfos.ForEach(v => worksheet.Cells[v.X + 1, v.Y + 1, v.X_Merge + 1, v.Y_Merge + 1].Merge = true);
                }

                var buffer = package.GetAsByteArray();
                return buffer;
            }
        }

        private static void SetCellStyle(ExcelRange cell,ExcelStyle excelStyle)
        {
            cell.Style.Font.Name = excelStyle.FontName;
            cell.Style.Font.Bold = excelStyle.IsBold;
            cell.Style.Font.Italic = excelStyle.IsItalic;
            cell.Style.Font.Strike = excelStyle.IsStrikeout;
            cell.Style.Font.Size = excelStyle.FontSize;//字体大小
            if (excelStyle.ExcelUnderLineType != ExcelUnderLineType.None)
            {
                cell.Style.Font.UnderLine = true;
                switch (excelStyle.ExcelUnderLineType)
                {
                    case ExcelUnderLineType.Single:
                        cell.Style.Font.UnderLineType = OfficeOpenXml.Style.ExcelUnderLineType.Single;
                        break;
                    case ExcelUnderLineType.SingleAccounting:
                        cell.Style.Font.UnderLineType = OfficeOpenXml.Style.ExcelUnderLineType.SingleAccounting;
                        break;
                    case ExcelUnderLineType.Double:
                        cell.Style.Font.UnderLineType = OfficeOpenXml.Style.ExcelUnderLineType.Double;
                        break;
                    case ExcelUnderLineType.DoubleAccounting:
                        cell.Style.Font.UnderLineType = OfficeOpenXml.Style.ExcelUnderLineType.DoubleAccounting;
                        break;
                    default:
                        cell.Style.Font.UnderLineType = OfficeOpenXml.Style.ExcelUnderLineType.None;
                        break;
                }
            }
        }

        /// <summary>
        /// 得到字符串长度，一个汉字长度为2
        /// </summary>
        /// <param name="inputString">参数字符串</param>
        /// <returns></returns>
        private static double StrLengthByASCII(string inputString, ASCIIEncoding ascii)
        {
            double tempLen = 0;
            byte[] s = ascii.GetBytes(inputString);
            for (int i = 0; i < s.Length; i++)
            {
                if (s[i] == 63)
                    tempLen += 2.61;
                else
                    tempLen += 1.22;
            }
            return tempLen;
        }
        
    }


}
